IF COL_LENGTH('ProductVariant', 'RackLocation') IS NULL
	ALTER TABLE ProductVariant add RackLocation nvarchar(100)

GO

if not exists 
	(select * from LocaleStringResource where ResourceName ='Admin.Catalog.Products.Variants.Fields.RackLocation')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'Admin.Catalog.Products.Variants.Fields.RackLocation','RackLocation')

if not exists 
	(select * from LocaleStringResource where ResourceName ='Admin.Catalog.Products.Variants.Fields.IsOpenBoxProductVariant')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'Admin.Catalog.Products.Variants.Fields.IsOpenBoxProductVarian','IsOpenBoxProductVariant')
	
if not exists 
	(select * from LocaleStringResource where ResourceName ='logistics.order.pendingorder.createnew')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'logistics.order.pendingorder.createnew','createnew')

if not exists 
	(select * from LocaleStringResource where ResourceName ='logistics.order.pendingorder.continuesaved')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'logistics.order.pendingorder.continuesaved','continuesaved')
	
if not exists 
	(select * from LocaleStringResource where ResourceName ='address.type.unknown')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'address.type.unknown','Unknown Address Type')



if not exists(select * from syscolumns where id=object_id('LogisticsPackage') and name='OrderKindId') 
alter table LogisticsPackage add OrderKindId int null
GO

update LogisticsPackage set OrderKindId=10 

update LogisticsPackage set OrderKindId=20
	where Id in 
		(select PackageId from Shipment
			 where OrderId in
				 (select Id from [Order] where OrderKindId=20))

update LogisticsPackage set OrderKindId=30
	where Id in 
		(select PackageId from Shipment
			 where OrderId in
				 (select Id from [Order] where OrderKindId=30))

GO
IF COL_LENGTH('LogisticsPackage', 'OrderKindId') IS NULL
	ALTER TABLE LogisticsPackage ALTER COLUMN OrderKindId int not null

GO
IF NOT EXISTS (select * from LocaleStringResource where ResourceName = 'Admin.Logistics.SelectedDistrict')
INSERT INTO LocaleStringResource (LanguageId,ResourceName,ResourceValue) VALUES (1, 'Admin.Logistics.SelectedDistrict','District')

IF NOT EXISTS (select * from LocaleStringResource where ResourceName = 'Admin.Logistics.SelectedTimeSpan')
INSERT INTO LocaleStringResource (LanguageId,ResourceName,ResourceValue) VALUES (1, 'Admin.Logistics.SelectedTimeSpan','Time Span')

IF NOT EXISTS(SELECT 1 from [dbo].[Setting] Where Name = 'customersettings.enablefreemembership')
INSERT [dbo].[Setting] ([Name],[Value])  VALUES ('customersettings.enablefreemembership', 'true')

GO
if not exists(select * from syscolumns where id=object_id('ShippingTimeWindows') and name='AllowSelectStartTime') 
alter table ShippingTimeWindows add AllowSelectStartTime int not null default 0
GO


if not exists(select * from syscolumns where id=object_id('ShippingTimeWindows') and name='AllowSelectEndTime') 
alter table ShippingTimeWindows add AllowSelectEndTime int not null default 0
GO

if not exists(select * from syscolumns where id=object_id('ShippingTimeWindows') and name='AllowOrderType') 
alter table ShippingTimeWindows add AllowOrderType varchar(100)  null
GO

if not exists(select * from syscolumns where id=object_id('ShippingTimeWindows') and name='AllowAddressType') 
alter table ShippingTimeWindows add AllowAddressType varchar(200)  null
GO




if not exists 
	(select * from LocaleStringResource where ResourceName ='Admin.Orders.Fields.ShipMentDate')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'Admin.Orders.Fields.ShipMentDate','ShipMentDate')

if not exists 
	(select * from LocaleStringResource where ResourceName ='Admin.Orders.Fields.ShipMentTimeRange')	
	insert into  LocaleStringResource  (LanguageId,ResourceName,ResourceValue) values (1,'Admin.Orders.Fields.ShippingTimeWindowsId','ShippingTimeWindowsId')



truncate table ShippingTimeWindows

SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] ON 
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (1, N'9 AM to 4 PM', 8, 16, 1, 0, 360, N'10', N'1')
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (2, N'4 PM to 9 PM', 16, 21, 2, 0, 12*60, N'10,20', N'1,2')
SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] OFF



UPDATE dbo.LocaleStringResource SET ResourceValue='ShipmentDate' WHERE ResourceName='Admin.Orders.Fields.ShipmentDate'
UPDATE dbo.LocaleStringResource SET ResourceValue='ShipmentTimeRange' WHERE ResourceName='Admin.Orders.Fields.ShipmentTimeRange'

GO
IF COL_LENGTH('Order', 'ShipMentDate') IS NOT NULL 
ALTER TABLE [dbo].[Order] DROP COLUMN ShipmentDate
GO

IF COL_LENGTH('Order','ShipMentTimeRange') IS NOT NULL
ALTER TABLE [dbo].[Order] DROP COLUMN ShipMentTimeRange
GO

IF COL_LENGTH('Order','ShippingTimeWindowsId') IS  NULL
ALTER TABLE [dbo].[Order] ADD ShippingTimeWindowsId INT NULL
GO

IF COL_LENGTH('[Order]','ShipmentDate')IS  NULL
ALTER TABLE [dbo].[Order] ADD ShipmentDate DATE NULL
GO

UPDATE dbo.[Order] SET ShippingTimeWindowsId=1,ShipmentDate=CAST('2015/8/1' AS DATE)

GO
ALTER TABLE [dbo].[Order] ALTER COLUMN ShippingTimeWindowsId INT NOT NULL
GO
ALTER TABLE [dbo].[Order] ALTER COLUMN ShipmentDate DATE NOT NULL 
GO

SET IDENTITY_INSERT [dbo].[CheckoutAttribute] ON 
IF NOT EXISTS 
	(SELECT * FROM [CheckoutAttribute] WHERE  [Name] ='receiver')	
	INSERT [dbo].[CheckoutAttribute] ([Id], [Name], [TextPrompt], [IsRequired], [ShippableProductRequired], [IsTaxExempt], [TaxCategoryId], [AttributeControlTypeId], [DisplayOrder]) VALUES (1, N'receiver', N'Receiver name', 0, 0, 1, 0, 4, 0)

IF NOT EXISTS 
	(SELECT * FROM [CheckoutAttribute] WHERE  [Name] ='sender')	
	 INSERT [dbo].[CheckoutAttribute] ([Id], [Name], [TextPrompt], [IsRequired], [ShippableProductRequired], [IsTaxExempt], [TaxCategoryId], [AttributeControlTypeId], [DisplayOrder]) VALUES (2, N'sender', N'sender name', 0, 0, 1, 0, 4, 1)

	
IF NOT EXISTS 
	(SELECT * FROM [CheckoutAttribute] WHERE  [Name] ='message')	
	INSERT [dbo].[CheckoutAttribute] ([Id], [Name], [TextPrompt], [IsRequired], [ShippableProductRequired], [IsTaxExempt], [TaxCategoryId], [AttributeControlTypeId], [DisplayOrder]) VALUES (3, N'message', N'message', 0, 0, 1, 0, 10, 0)
SET IDENTITY_INSERT [dbo].[CheckoutAttribute] OFF

IF NOT EXISTS 
	(SELECT * FROM [CustomerRole] WHERE  [Name] ='eCommerce Logistics Manager')	
	INSERT [dbo].[CustomerRole] ([Name],[FreeShipping],[TaxExempt],[Active],[IsSystemRole],[SystemName]) 
	VALUES (N'eCommerce Logistics Manager',1,0,1,0,N'eCommerce Logistics Manager')

GO
IF COL_LENGTH('Product', 'AgeClass') IS NULL
BEGIN
	ALTER TABLE Product add AgeClass TinyInt NOT NULL Default 0	
END
GO

IF NOT EXISTS (select * from LocaleStringResource where ResourceName = 'Admin.Catalog.Products.Fields.AgeClass')
INSERT INTO LocaleStringResource (LanguageId,ResourceName,ResourceValue) VALUES (1, 'Admin.Catalog.Products.Fields.AgeClass','Age Class')

UPDATE dbo.LocaleStringResource SET ResourceName='Admin.Orders.Fields.ShippingTimeWindowsId',ResourceValue='ShippingTimeWindowsId' 
WHERE Id=(SELECT Id FROM dbo.LocaleStringResource WHERE ResourceName='Admin.Orders.Fields.ShipmentTimeRange')


IF NOT EXISTS (SELECT 1 FROM [dbo].[LocaleStringResource] WHERE ResourceName = 'Order.CancelOrder')
INSERT INTO [dbo].[LocaleStringResource]
           ([LanguageId]
           ,[ResourceName]
           ,[ResourceValue])
     VALUES (1,'Order.CancelOrder','Cancel Order')

IF NOT EXISTS (SELECT 1 FROM [dbo].[LocaleStringResource] WHERE ResourceName = 'Order.ReturnRequest')
INSERT INTO [dbo].[LocaleStringResource]
           ([LanguageId]
           ,[ResourceName]
           ,[ResourceValue])
     VALUES (1,'Order.ReturnRequest','Request Return')
		   
		   

if NOT EXISTS(select * from PermissionRecord where SystemName='OrderKind' and Category='Logistics')
begin
	Insert into PermissionRecord (Name,Systemname,Category)values('Admin area .OrderKind ','OrderKind','Logistics')
end
 
declare @pid int,@cid int
set @pid=0
set @cid=0
if EXISTS(select * from PermissionRecord where SystemName='OrderKind' and Category='Logistics')
begin
	 select @pid=Id from PermissionRecord where SystemName='OrderKind' and Category='Logistics'
end
if EXISTS(select * from CustomerRole where SystemName='eCommerce Logistics Manager')
begin
	 select @cid=Id from CustomerRole where SystemName='eCommerce Logistics Manager'
end
if (@pid<>0 and @cid<>0)
begin
	if NOT EXISTS(select * from PermissionRecord_Role_Mapping where PermissionRecord_Id=@pid and CustomerRole_Id=@cid)
	insert into PermissionRecord_Role_Mapping(PermissionRecord_Id,CustomerRole_Id)values(@pid,@cid)
end


Update Setting Set Value = '30' Where Name = 'ordersettings.numberofdaysreturnrequestavailable'

IF NOT EXISTS 
	(SELECT * FROM dbo.LocaleStringResource WHERE  ResourceName='Admin.Orders.PdfMessageLabel')
	INSERT dbo.LocaleStringResource
	        ( LanguageId ,
	          ResourceName ,
	          ResourceValue
	        )
	VALUES  ( 1 , -- LanguageId - int
	          N'Admin.Orders.PdfMessageLabel' , -- ResourceName - nvarchar(200)
	          N'MessageLabel(PDF)'  -- ResourceValue - nvarchar(max)
	        )

if NOT EXISTS(select * from LocaleStringResource where ResourceName='Admin.Orders.PrintPackingSummary' and ResourceValue='Print Packing Summary')
Insert into LocaleStringResource(LanguageId,ResourceName,ResourceValue)
values(1,'Admin.Orders.PrintPackingSummary','Print Packing Summary')


TRUNCATE TABLE dbo.ShippingTimeWindows
GO
SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] ON 

INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (1, N'8 AM to 16 PM', 8, 16, 1, 0, 840, N'10,20,30', N'1,2')
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (2, N'16 PM to 21 PM', 16, 21, 2, 0, 840, N'10,20,30', N'2')
SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] OFF


IF NOT EXISTS (select * from LocaleStringResource where ResourceName = 'Admin.Customers.Customers.Report.MonthOfBirthday')
INSERT INTO LocaleStringResource (LanguageId,ResourceName,ResourceValue) VALUES (1, 'Admin.Customers.Customers.Report.MonthOfBirthday','MonthOfBirthday')

IF NOT EXISTS (select * from LocaleStringResource where ResourceName = 'Admin.Customers.Reports.CustomerBirthday')
INSERT INTO LocaleStringResource (LanguageId,ResourceName,ResourceValue) VALUES (1, 'Admin.Customers.Reports.CustomerBirthday','Customers birthday')

IF NOT EXISTS(SELECT 1 from dbo.Topic WHERE systemName like 'api%')
	INSERT INTO dbo.[Topic] ([SystemName]
      ,[IncludeInSitemap]
      ,[IsPasswordProtected]
      ,[Password]
      ,[Title]
      ,[Body]
      ,[MetaKeywords]
      ,[MetaDescription]
      ,[MetaTitle]
      ,[PictureId])
	  VALUES('ApiShippingInfo',0,0,NULL,'Shipping Info','<p>Shipping Info</p>',NULL,NULL,NULL,NULL),
	  ('ApiProductPolicy',0,0,NULL,'Product Policy','<p>SpotsHUB Product Policy</p>',NULL,NULL,NULL,NULL),
	  ('ApiContactUs',0,0,NULL,'Contact Us','<p>Contact Us</p>',NULL,NULL,NULL,NULL),
	  ('ApiStarterGuide',0,0,NULL,'Starter Guide','<p>Starter Guide</p>',NULL,NULL,NULL,NULL),
	  ('ApiShipping',0,0,NULL,'Shipping','<p>Shipping</p>',NULL,NULL,NULL,NULL),
	  ('ApiReturnsPolicy',0,0,NULL,'Return Policy','<p>Return Policy</p>',NULL,NULL,NULL,NULL),
	  ('ApiTermsOfUse',0,0,NULL,'Terms of Use','<p>Terms Of use</p>',NULL,NULL,NULL,NULL),
	  ('ApiCopyright',0,0,NULL,'Copy Right','<p>Copy Right</p>',NULL,NULL,NULL,NULL),
	  ('ApiMAPPPolicy',0,0,NULL,'Mobile App Policy','<p>Mobile App Policy</p>',NULL,NULL,NULL,NULL)

IF NOT EXISTS(select * from Setting where Name='globalpaymentssettings.gtpaysecureurl')
Insert into Setting(Name,Value)values('globalpaymentssettings.gtpaysecureurl','https://direct.sandbox-gtpaysecure.net/direct/services/request/query/?CRESecureID={0}&CRESecureAPIToken={1}&uID={2}')



IF NOT EXISTS 
	(SELECT * FROM dbo.Setting WHERE Name ='addresssettings.ZipPostalCodePrefixValue')
	INSERT INTO dbo.Setting
			( Name, Value )
	VALUES  ( N'addresssettings.ZipPostalCodePrefixValue', -- Name - nvarchar(200)
			  N'V6T,V6S,V6N,V7B,V7C,V7E,V6R,V6K,V6L,V6J,V6M,V6P,V6X,V6Y,V7A,V6G,V6H,V5Y,V5Z,V6E,V6Z,V6B,V6C,V6A,V7X,V7Y,V5T,V5V,V5W,V5X,V5L,V5N,V5P,V5K,V5M,V5R,V5S,V6V,V6W,V5C,V5G,V5H,V5J,V5B,V5A,V5E,V3N,V3L,V3M,V3H,V3J,V3K,V3E,V3B,V3C

	'  -- Value - nvarchar(2000)
			  )

IF EXISTS 
	(select * from LocaleStringResource where ResourceName ='ContactUs.YourEnquiryHasBeenSent')	
	 UPDATE dbo.LocaleStringResource SET ResourceValue='Thank you, your msessage has been succefully submitted. Someone from SpotsHUB will be in touch with you shortly'
	 WHERE ResourceName='ContactUs.YourEnquiryHasBeenSent'


TRUNCATE TABLE dbo.ShippingTimeWindows
GO
SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] ON 
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (1, N'8 AM to 12 PM', 8, 12, 1, 0, 840, N'10,20,30', N'1,2')
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (2, N'12 AM to 4 PM', 12, 16, 2, 0, 840, N'10,20,30', N'1,2')
INSERT [dbo].[ShippingTimeWindows] ([Id], [Description], [NoEarlyThan], [NoLateThan], [DisplayOrder], [AllowSelectStartTime], [AllowSelectEndTime], [AllowOrderType], [AllowAddressType]) VALUES (3, N'4 PM to 9 PM', 16, 21, 3, 0, 840, N'10,20,30', N'2')
SET IDENTITY_INSERT [dbo].[ShippingTimeWindows] OFF

GO
Update MessageTemplate Set EmailAccountId = 6 WHERE EmailAccountId NOT IN (6,7)


IF NOT EXISTS (SELECT * FROM dbo.MessageTemplate WHERE Name = 'ContactUs.Confirmation')
INSERT INTO dbo.MessageTemplate
        ( Name ,
          BccEmailAddresses ,
          Subject ,
          Body ,
          IsActive ,
          EmailAccountId
        )
VALUES  ( N'ContactUs.Confirmation' , -- Name - nvarchar(200)
          N'little_deer@hotmail.com' , -- BccEmailAddresses - nvarchar(200)
          N'SpotsHUB-Contact Us Confirm' , -- Subject - nvarchar(1000)
          N'<p>Dear Customer,</p><p><br /> Thank you, your msessage has been succefully submitted. Someone from SpotsHUB will be in touch with you shortly</p><p></p><p>Sincerely,</p><p>SpotsHUB Customer Service Team <br /><a href="mailto:express@spotshub.com">express@spotshub.com</a> <br />Unit 19 -&nbsp;91 Golden Drive, Coquitlam, BC V3K 6R2</p><p>Note: This is an automated message. Please do not reply to this email. For further inquiries, please contact us at express@spotshub.com.</p>' , -- Body - nvarchar(max)
          1 , -- IsActive - bit
          6  -- EmailAccountId - int
        )


IF NOT EXISTS (SELECT * FROM dbo.MessageTemplate WHERE Name = 'ContactUs.CostumerSendEmail')
INSERT INTO dbo.MessageTemplate
        ( Name ,
          BccEmailAddresses ,
          Subject ,
          Body ,
          IsActive ,
          EmailAccountId
        )
VALUES  ( N'ContactUs.CostumerSendEmail' , -- Name - nvarchar(200)
          N'' , -- BccEmailAddresses - nvarchar(200)
          N'Contact Us' , -- Subject - nvarchar(1000)
          N'<p><strong>From:</strong><span>%Customer.FullName%</span></p><p><span><span>%ContactUs.PersonalMessage%</span></span></p>' , -- Body - nvarchar(max)
          1 , -- IsActive - bit
          6  -- EmailAccountId - int
        )

GO

IF COL_LENGTH('Product','ShippingReturnTopicName') IS  NULL
ALTER TABLE [dbo].Product ADD ShippingReturnTopicName NVARCHAR(50) NULL
GO

IF NOT EXISTS (SELECT 1 FROM LocaleStringResource WHERE ResourceName='Admin.Catalog.Products.Fields.ShippingReturnTopic')
INSERT INTO LocaleStringResource (LanguageId, ResourceName, ResourceValue) 
	VALUES(1, 'Admin.Catalog.Products.Fields.ShippingReturnTopic','Shipping Return Policy')

IF NOT EXISTS(SELECT 1 FROM SETTING WHERE Name = 'catalogsettings.defaultshippingreturntopic')
	INSERT INTO SETTING (Name,Value) VALUES('catalogsettings.defaultshippingreturntopic','ShippingInfo')
IF NOT EXISTS
	(SELECT * FROM Setting WHERE NAME = 'facebookExternalJSSettings.clientKeyIdentifier')
	INSERT INTO Setting(Name,Value) Values('facebookExternalJSSettings.clientKeyIdentifier','651305524971353')


IF NOT EXISTS 
	 (SELECT * FROM Setting WHERE NAME = 'facebookExternalJSSettings.clientSecret')
	 INSERT INTO Setting(Name,Value) Values('facebookExternalJSSettings.clientSecret','0a5053059b2f6becea0738277e6fd2fe')

IF NOT EXISTS 
     (SELECT * FROM Setting WHERE NAME = 'homePageUrl')
	 INSERT INTO Setting(Name,Value) Values('homePageUrl','http://spotshubstage.azurewebsites.net/')


IF COL_LENGTH('Store', 'RegionId') IS  NULL 
ALTER TABLE [dbo].Store ADD RegionId INT DEFAULT 1 NOT NULL  
GO


IF NOT EXISTS (SELECT 1 FROM LocaleStringResource WHERE ResourceName='Admin.Catalog.Stores.Fields.RegionId')
INSERT INTO LocaleStringResource (LanguageId, ResourceName, ResourceValue) 
	VALUES(1, 'Admin.Catalog.Stores.Fields.RegionId','Region')

IF  EXISTS 
	(SELECT * FROM [CheckoutAttribute] WHERE  [Name] ='message')
	UPDATE dbo.CheckoutAttribute SET AttributeControlTypeId=10 WHERE Name='message'	

IF EXISTS(SELECT id FROM [dbo].[Setting] WHERE Value='Replacement,Store Credit')
	UPDATE [dbo].[Setting] SET Value='Replacement,Refund' WHERE Id=(SELECT id FROM [dbo].[Setting] WHERE Value='Replacement,Store Credit')

GO

IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'ProductNeverSold')
BEGIN
	DROP PROCEDURE ProductNeverSold
END
GO
CREATE PROCEDURE [dbo].[ProductNeverSold]
(
@startDate DateTime,
@endDate DateTime,
@pageIndex int = 0,
@pageSize int = 30,
@showHidden bit = 0,
@store int = 1,
@totalRecords int = null OUTPUT
)
AS
BEGIN

	;WITH PV_CTE AS(
	SELECT PV.* FROM ProductVariant PV 
	LEFT JOIN 
	(
	  SELECT opv.ProductVariantId FROM OrderProductVariant opv INNER JOIN [order] o on o.id = opv.orderId 
	  where o.deleted = 0 AND ( @startDate IS NULL OR o.CreatedOnUtc>=@startDate) AND ( @endDate IS NULL OR o.CreatedOnUtc<=@endDate)
	) as T
	ON t.ProductVariantId = pv.Id
	INNER JOIN Product P ON Pv.ProductId = P.Id
	WHERE t.ProductVariantId IS NULL AND pv.deleted = 0 AND p.deleted = 0 AND (@showHidden = 1 OR pv.published = 1) AND (@showHidden = 1 OR P.published = 1) 
	)

	SELECT PV.* FROM PV_CTE as PV	
	ORDER by PV.Id OFFSET @PageIndex*@PageSize ROW FETCH NEXT @PageSize ROWS only;

	;WITH PV_CTE AS(
	SELECT PV.* FROM ProductVariant PV 
	LEFT JOIN 
	(
	  SELECT opv.ProductVariantId FROM OrderProductVariant opv INNER JOIN [order] o on o.id = opv.orderId 
	  where o.deleted = 0 AND ( @startDate IS NULL OR o.CreatedOnUtc>=@startDate) AND ( @endDate IS NULL OR o.CreatedOnUtc<=@endDate)
	) as T
	ON t.ProductVariantId = pv.Id
	INNER JOIN Product P ON Pv.ProductId = P.Id
	WHERE t.ProductVariantId IS NULL AND pv.deleted = 0 AND p.deleted = 0 AND (@showHidden = 1 OR pv.published = 1) AND (@showHidden = 1 OR P.published = 1) 
	)

	SELECT @totalRecords = COUNT(1)  FROM PV_CTE
END

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'IX_Order_DELETED')  /*Optimize the query performance for above procedure*/
CREATE NONCLUSTERED Index IX_Order_DELETED ON [dbo].[order] ([Deleted]) INCLUDE ([ID],[CreatedOnUtc])


IF NOT EXISTS
	(
		SELECT COUNT(1) FROM Setting WHERE Name = 'display.limitMessage'
	)
   INSERT INTO Setting(Name,Value) VALUES('display.limitMessage',10)

IF NOT EXISTS
	(
		SELECT * FROM localeStringResource WHERE ResourceName = 'Admin.Orders.Report.SubtotalExclTax' and LanguageId = 1
	)
	INSERT INTO localeStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'Admin.Orders.Report.SubtotalExclTax','Subtotal ExclTax')

IF NOT EXISTS
	(
		SELECT * FROM localeStringResource WHERE ResourceName = 'Admin.Orders.Report.FuelSurcharge' and LanguageId = 1
	)
	INSERT INTO localeStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'Admin.Orders.Report.FuelSurcharge','Fuel Surcharge')



IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'account.customerorders.shippingmethod' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'account.customerorders.shippingmethod','Shipping Method')



IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'account.customerorders.paymentmethod' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'account.customerorders.paymentmethod','Payment Method')



IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'account.customeraddressesedit' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'account.customeraddressesedit','Edit Addresses')

IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'account.customeraddressesdeleteprompt' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'account.customeraddressesdeleteprompt','Delete Addresses')

IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'account.customersettings' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'account.customersettings','Customer Setting')

IF NOT EXISTS
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'address.type.business' 
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'address.type.business','Business Address')

IF NOT EXISTS 
	(SELECT * FROM dbo.LocaleStringResource WHERE ResourceName ='address.type.residential')	
	INSERT INTO  dbo.LocaleStringResource
	        ( LanguageId ,
	          ResourceName ,
	          ResourceValue
	        )
	VALUES  ( 1 , -- LanguageId - int
	          N'address.type.residential' , -- ResourceName - nvarchar(200)
	          N'residential'  -- ResourceValue - nvarchar(max)
	        ) 

IF NOT EXISTS (SELECT * FROM ScheduleTask where Name = 'Remove pending order')
INSERT INTO dbo.ScheduleTask
        ( Name ,
          Seconds ,
          Type ,
          Enabled ,
          StopOnError ,
          LastStartUtc ,
          LastEndUtc ,
          LastSuccessUtc
        )
VALUES  ( N'Remove pending order' , -- Name - nvarchar(max)
          9000 , -- Seconds - int
          N'Nop.Services.Orders.QueueOrderPendingRemoveTask,Nop.Services' , -- Type - nvarchar(max)
          1 , -- Enabled - bit
          0 , -- StopOnError - bit
          '2015-09-02 03:42:35' , -- LastStartUtc - datetime
          '2015-09-02 03:42:35' , -- LastEndUtc - datetime
          '2015-09-02 03:42:35'  -- LastSuccessUtc - datetime
        )

IF NOT EXISTS 
	(
		SELECT * FROM LocaleStringResource WHERE ResourceName = 'orderdetial.ifDisableButton'
	)
	INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) VALUES(1,'orderdetial.ifDisableButton','Sold Out');


IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='account.customerorders.paymentstatus')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'account.customerorders.paymentstatus' , -- ResourceName - nvarchar(200)
          N'Pay Status'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='account.customerorders.shippingmethod')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'account.customerorders.shippingmethod' , -- ResourceName - nvarchar(200)
          N'Shipping Method'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='account.customerorders.paymentmethod')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'account.customerorders.paymentmethod' , -- ResourceName - nvarchar(200)
          N'Payment Method'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.orders.fields.partialrefund.orderproductvariant.refundquantity')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'admin.orders.fields.partialrefund.orderproductvariant.refundquantity' , -- ResourceName - nvarchar(200)
          N'refundquantity'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.orders.fields.partialrefund.orderproductvariant.subtotalexcltax')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'admin.orders.fields.partialrefund.orderproductvariant.subtotalexcltax' , -- ResourceName - nvarchar(200)
          N'subtotalexcltax'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.orders.fields.partialrefund.orderproductvariant.discountexcltax')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'admin.orders.fields.partialrefund.orderproductvariant.discountexcltax' , -- ResourceName - nvarchar(200)
          N'discountexcltax'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.orders.fields.partialrefund.orderproductvariant.isrefund')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'admin.orders.fields.partialrefund.orderproductvariant.isrefund' , -- ResourceName - nvarchar(200)
          N'isrefund'  -- ResourceValue - nvarchar(max)
        )


IF NOT EXISTS
	(
		select  * from LocaleStringResource where resourcename = 'orderdetial.ifDisableButton'
	)

INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue) Values(1,'orderdetial.ifDisableButton','Product are sold out')

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='order.shipments.scheduledelivery')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'order.shipments.scheduledelivery' , -- ResourceName - nvarchar(200)
          N'Schedule Delivery'  -- ResourceValue - nvarchar(max)
        )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='logistic.tracking.warehousepickup')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'logistic.tracking.warehousepickup' , -- ResourceName - nvarchar(200)
          N'Warehouse Pickup'  -- ResourceValue - nvarchar(max)
        )


IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='account.customerorders.paymentstatus')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'account.customerorders.paymentstatus' , -- ResourceName - nvarchar(200)
          N'Payment Status'  -- ResourceValue - nvarchar(max)
        )